using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public TitpaghistVO getTitpaghist(int? idTitPagHist, string titPagHist)
		{
			TitpaghistVO titpaghistVO = null;
			List<TitpaghistVO> listTitpaghistVOs = getTitpaghists(idTitPagHist, titPagHist, null);
			if (listTitpaghistVOs != null && listTitpaghistVOs.Count > 0)
			{
				titpaghistVO = listTitpaghistVOs[0];
			}
			return titpaghistVO;
		}

		public TitpaghistVO getTitpaghist(int? idTitPagHist)
		{
			return getTitpaghist(idTitPagHist, null);
		}

		public List<TitpaghistVO> getTitpaghists()
		{
			return getTitpaghists(null, null, null);
		}

		public List<TitpaghistVO> getTitpaghists(int? idTitPagHist)
		{
			return getTitpaghists(idTitPagHist, null, null);
		}

		public List<TitpaghistVO> getTitpaghists(int? idTitPagHist, string titPagHist)
		{
			return getTitpaghists(idTitPagHist, titPagHist, null);
		}

		public List<TitpaghistVO> getTitpaghists(int? idTitPagHist, string titPagHist, int? idTitPag)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT tph.idTitPagHist as tph_idTitPagHist, tph.idTitPag as tph_idTitPag, ttp.nrDoc as ttp_nrDoc, tph.titPagHist as tph_titPagHist, tph.titPagAcao as tph_titPagAcao, tph.cadastro as tph_cadastro, tph.cadastradoPor as tph_cadastradoPor, tph.edicao as tph_edicao, tph.editadoPor as tph_editadoPor  ");
			query.AppendLine("FROM titpaghist as tph LEFT OUTER JOIN titpag as ttp on tph.idTitPag=ttp.idTitPag  ");
			if (idTitPagHist != null)
			{
				query.AppendLine(where + "tph.idTitPagHist=@idTitPagHist ");
				where = "AND ";
			}
			if (idTitPag != null)
			{
				query.AppendLine(where + "tph.idTitPag=@idTitPag ");
				where = "AND ";
			}
			if (titPagHist != null)
			{
				query.AppendLine(where + "tph.titPagHist LIKE @titPagHist ");
				where = "AND ";
			}


            query.AppendLine("ORDER BY tph.idTitPagHist DESC ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idTitPagHist!= null) cmd.Parameters.Add(getParameter(cmd, "@idTitPagHist", idTitPagHist));
			if (idTitPag!= null) cmd.Parameters.Add(getParameter(cmd, "@idTitPag", idTitPag));
			if (titPagHist!= null) cmd.Parameters.Add(getParameter(cmd, "@titPagHist", "%"+titPagHist+"%"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<TitpaghistVO> listTitpaghist = new List<TitpaghistVO>();

				while (reader.Read())
				{
					TitpaghistVO titpaghistVO = TitpaghistVO.CreateVO();
					titpaghistVO.idTitPagHist = reader.GetInt32(reader.GetOrdinal("tph_idTitPagHist"));
					titpaghistVO.idTitPag = reader.GetInt32(reader.GetOrdinal("tph_idTitPag"));
					if (!reader.IsDBNull(reader.GetOrdinal("ttp_nrDoc")))
						titpaghistVO.idTitPagVO.nrDoc = reader.GetString(reader.GetOrdinal("ttp_nrDoc"));
					titpaghistVO.titPagHist = reader.GetString(reader.GetOrdinal("tph_titPagHist"));
					titpaghistVO.titPagAcao = reader.GetString(reader.GetOrdinal("tph_titPagAcao"));
					titpaghistVO.cadastro = reader.GetDateTime(reader.GetOrdinal("tph_cadastro"));
					titpaghistVO.cadastradoPor = reader.GetString(reader.GetOrdinal("tph_cadastradoPor"));
					titpaghistVO.edicao = reader.GetDateTime(reader.GetOrdinal("tph_edicao"));
					titpaghistVO.editadoPor = reader.GetString(reader.GetOrdinal("tph_editadoPor"));


					listTitpaghist.Add(titpaghistVO);
				}
				reader.Close();
				return listTitpaghist;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setTitpaghist(TitpaghistVO titpaghistVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE titpaghist ");
			query.AppendLine("SET idTitPag=@idTitPag, titPagHist=@titPagHist, titPagAcao=@titPagAcao,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idTitPagHist=@idTitPagHist ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idTitPagHist", titpaghistVO.idTitPagHist));
			cmd.Parameters.Add(getParameter(cmd, "@idTitPag", titpaghistVO.idTitPag));
			cmd.Parameters.Add(getParameter(cmd, "@titPagHist", titpaghistVO.titPagHist));
			cmd.Parameters.Add(getParameter(cmd, "@titPagAcao", titpaghistVO.titPagAcao));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", titpaghistVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", titpaghistVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", titpaghistVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", titpaghistVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newTitpaghist(TitpaghistVO titpaghistVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO titpaghist ");
			query.AppendLine("  ( idTitPag, titPagHist, titPagAcao, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @idTitPag, @titPagHist, @titPagAcao, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idTitPag", titpaghistVO.idTitPag));
			cmd.Parameters.Add(getParameter(cmd, "@titPagHist", titpaghistVO.titPagHist));
			cmd.Parameters.Add(getParameter(cmd, "@titPagAcao", titpaghistVO.titPagAcao));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", titpaghistVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", titpaghistVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", titpaghistVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", titpaghistVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('titpaghist') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delTitpaghist(TitpaghistVO titpaghistVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM titpaghist ");
            if (titpaghistVO.idTitPagHist != -1) query.AppendLine("WHERE idTitPagHist=@idTitPagHist ");
            if (titpaghistVO.idTitPag != -1) query.AppendLine("WHERE idTitPag=@idTitPag ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

            if (titpaghistVO.idTitPagHist != -1) cmd.Parameters.Add(getParameter(cmd, "@idTitPagHist", titpaghistVO.idTitPagHist));
            if (titpaghistVO.idTitPag != -1) cmd.Parameters.Add(getParameter(cmd, "@idTitPag", titpaghistVO.idTitPag));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountTitpaghistxIdTitPag(int? idTitPag)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idTitPag) as ocorrencias FROM titpaghist WHERE IdTitPag=@IdTitPag ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdTitPag", idTitPag));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
